package io.bdmc.common.utils;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelImport {
    /**
     * @param fis         表格文件
     * @param fileEndWith 一行多少个单元格
     * @return 返回list集合
     */
    public static List<Map<String, String>> read(InputStream fis, String fileEndWith) throws Exception {
        Workbook wookbook = null;
        if (fileEndWith.equalsIgnoreCase("xls")) {
            wookbook = new HSSFWorkbook(fis);// 得到工作簿
        } else if (fileEndWith.toLowerCase().equals("xlsx")) {
            wookbook = new XSSFWorkbook(fis);
        }
        fis.close();
        if (wookbook == null)
            return null;
        // 得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);
        // 获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();
        // 获得数据的总列数
        int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
        // 要获得属性
        List<Map<String, String>> list = new ArrayList<>();
        Map<String, String> map;
        // 获得所有数据
        // 从第x行开始获取
        for (int x = 1; x <= totalRowNum; x++) {
            map = new HashMap<>();
            // 获得第i行对象
            Row row = sheet.getRow(x);
            for (int y = 0; y < coloumNum; y++) {
                Cell cell = row.getCell(y);
                String strtype = sheet.getRow(0).getCell(y).getStringCellValue();
                if (StringUtils.isEmpty(strtype)) {
                    continue;
                }
                if (cell == null) {
                    map.put(strtype, "");
                } else {
                    map.put(strtype, cell.getStringCellValue());
                }
            }
            list.add(map);
        }
        return list;
    }

    public static List<Map<String, String>> read(InputStream fis, String fileEndWith, int dataStartRow, int totalRow,
            int typeRow) throws Exception {
        Workbook wookbook = null;
        if (fileEndWith.equalsIgnoreCase("xls")) {
            wookbook = new HSSFWorkbook(fis);// 得到工作簿
        } else if (fileEndWith.equalsIgnoreCase("xlsx")) {
            wookbook = new XSSFWorkbook(fis);
        }
        fis.close();
        if (wookbook == null)
            return null;
        // 得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);
        // 获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();
        if (totalRow == 0) {
            totalRowNum = sheet.getLastRowNum() - 3;
        } else {
            totalRowNum = totalRow;
        }

        // 获得数据的总列数
        int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
        // 要获得属性
        List<Map<String, String>> list = new ArrayList<>();
        Map<String, String> map ;
        // 获得所有数据
        // 从第x行开始获取
        for (int x = dataStartRow; x <= totalRowNum; x++) {
            map = new HashMap<>();
            // 获得第i行对象
            Row row = sheet.getRow(x);
            for (int y = 0; y < coloumNum; y++) {
                Cell cell = row.getCell(y);
                String strtype = sheet.getRow(typeRow).getCell(y).getStringCellValue();
                if (StringUtils.isEmpty(strtype)) {
                    continue;
                }
                if (strtype.contains("设施类型")) {
                    strtype = "设施类型";
                }
                if (cell == null) {
                    map.put(strtype, "");
                } else {
                    // cell.setCellType(cellType);
                    if(cell.getCellType().equals(CellType.NUMERIC)){
                        map.put(strtype, cell.getNumericCellValue()+"");
                    }else{
                        map.put(strtype, cell.getStringCellValue());
                    }

                }
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 创建表格样式
     * 
     * @param wb 工作薄对象
     * @return 样式列表
     */
    public static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<>();

        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        // titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data3", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        // style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        // style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        style.setFont(headerFont);
        style.setFillForegroundColor(HSSFColorPredefined.WHITE.getIndex());
        styles.put("header", style);

        return styles;
    }

    public static int initialize(int rownum, String title, String[] headers, HSSFWorkbook wb, Sheet sheet,
            Map<String, CellStyle> styles) {
        // Create title
        if (!StringUtils.isEmpty(title)) {
            Row titleRow = sheet.createRow(rownum++);
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(styles.get("title"));
            titleCell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
                    headers.length - 1));
        }
        // Create header
        Row headerRow = sheet.createRow(rownum++);
        headerRow.setHeightInPoints(16);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellStyle(styles.get("header"));
            cell.setCellValue(text);
            sheet.autoSizeColumn(i);
        }
        for (int i = 0; i < headers.length; i++) {
            int colWidth = sheet.getColumnWidth(i) * 2;
            sheet.setColumnWidth(i, Math.max(colWidth, 3000));
        }
        return rownum;
    }

    public static String convertNullString(Object data) {
        if(data ==null) return "";
        if (StringUtils.isEmpty(data.toString())) {
            return "";
        } else {
            if (data instanceof LocalDateTime) {
                return DateUtils.formatLocalDateTimeDefault((LocalDateTime) data);
            }
            return data.toString();
        }
    }

}
